package db;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

import util.Number;

import model.City;
import model.Country;
import model.Customer;
import model.Service;

public class DBService implements IFService
{
	private Connection con;

	public DBService()
		{
			con = DBConnection.getInstance().getDBcon();
		}

	private Service buildService(ResultSet results) throws SQLException
	{
		Service service = new Service();

		service.setServiceID(results.getInt("service_id"));
		service.setDrawingNo(results.getInt("service_drawing_number"));
		service.setName(results.getString("service_name"));
		service.setProdQuantity(results.getInt("service_prod_quantity"));
		service.setDescription(results.getString("service_description"));
		service.setDimensions(results.getString("service_dimensions"));
		service.setDeadline(results.getString("service_deadline"));

		return service;

	}

	private Customer buildCustomer(ResultSet results) throws SQLException
	{
		Customer customer = new Customer();

		customer.setPersonID(results.getInt("customer_id"));
		customer.setUsername(results.getString("person_username"));


		return customer;

	}

	private City buildCity(ResultSet results) throws SQLException
	{
		City city = new City();

		city.setCityID(results.getInt("city_id"));
		city.setName(results.getString("city_name"));

		return city;
	}

	private Country buildCountry(ResultSet results) throws SQLException
	{
		Country country = new Country();

		try
			{
				country.setCountryID(results.getInt("country_id"));
				country.setName(results.getString("country_name"));
				country.setTld(results.getString("country_tld"));
			} // end of try
		catch(Exception e)
			{
				System.out.println("could not build the country, error : " + e);
			} // end of catch

		return country;
	}

	private ArrayList<Service> searchService(boolean type,boolean active, String clause, int num, String search)
	{
		ArrayList<Service> serviceList = new ArrayList<Service>();

		ResultSet results;

		Service service = new Service();
		Country country = new Country();
		Customer customer = new Customer();
		City city = new City();

		String query = "SELECT s.*,so.*, p.person_username, c.city_name, c.city_id, ct.country_name, ct.country_id, ct.country_tld," +
				" cus.customer_city_id, cus.customer_id FROM service AS s LEFT JOIN offer AS so ON s.service_id = so.offer_service_id LEFT JOIN person AS p ON s.service_customer_id = p.person_id" +
				" LEFT JOIN customer AS cus ON p.person_id = cus.customer_id LEFT JOIN city AS c ON c.city_id = cus.customer_city_id" +
				"  LEFT JOIN country AS ct ON c.city_country_id = ct.country_id WHERE "+(type ? "s.service_type = 1" : "s.service_type = 0") + (active ? "" +
						" AND s.service_active = '1'" : "") + ((clause.length() > 0 ) ? (" AND " + clause) : "") +
						" ORDER BY p.person_username ASC;";

		if(DBConnection.doDebug())
			DBConnection.printDebug("searchService", query);

		try
			{
				PreparedStatement ps = con.prepareStatement(query);


				if(clause.length() > 0)
					{
						ps.setString(1, (num == -1 ? search : "%" + search + "%"));

						if( num > 1)
							{
								for(int i = 1; i <= num ; i++)
									{
										ps.setString(i, (i == 16 ? search : "%" + search + "%"));
									} // end of for
							} // end of if
					} // end of if
				results = ps.executeQuery();
				while(results.next())
					{
						service = buildService(results);
						city = buildCity(results);
						country = buildCountry(results);
						customer = buildCustomer(results);

						city.setCountry(country);
						customer.setCity(city);
						service.setCustomer(customer);
						
						if(type)
							service.setPrice(results.getDouble("offer_price"));

						serviceList.add(service);

					} // end of while

				ps.close();
			} // end of try
		catch(Exception e)
			{
				if(DBConnection.doDebug())
					{
						DBConnection.printDebug("searchService", query);
						System.out.println("error query exception - searchService - SELECT: " + e);
						e.printStackTrace();
					} // end of if
			} // end of catch

		return serviceList;
	}

	@Override
	public ArrayList<Service> searchServicesByAllInfo(boolean type,
			boolean active, String search) throws SQLException
			{
				boolean number = Number.isNumber(search);
				return searchService(type, active, "(s.service_name LIKE ? OR s.service_type LIKE ? OR s.service_prod_quantity LIKE ? OR s.service_description LIKE ? OR s.service_drawing_number LIKE ? OR s.service_dimensions LIKE ? OR s.service_deadline LIKE ? OR c.city_name LIKE ? OR ct.country_name LIKE ? OR ct.country_tld LIKE ? OR p.person_username LIKE ?" + (number ? "OR s.service_id = ?" : "") + " ) ", (number ? 12 : 11), search);
			}

	@Override
	public ArrayList<Service> getAllServices(boolean type, int active) throws SQLException
	{
		ArrayList<Service> serviceList = new ArrayList<Service>();

		ResultSet results;

		Service service = new Service();
		Country country = new Country();
		Customer customer = new Customer();
		City city = new City();

		String query = "SELECT s.*,so.*, p.person_username, p.person_email, p.person_phoneno, c.city_name, c.city_id, ct.country_name,ct.country_id, ct.country_tld," +
				" cus.customer_city_id, cus.customer_id FROM service AS s LEFT JOIN offer AS sp ON s.service_id = sp.offer_service_id LEFT JOIN person AS p ON s.service_customer_id = p.person_id" +
				" LEFT JOIN customer AS cus ON p.person_id = cus.customer_id LEFT JOIN city AS c ON c.city_id = cus.customer_city_id" +
				"  LEFT JOIN country AS ct ON c.city_country_id = ct.country_id WHERE " + (type ? "s.service_type = 1" : "s.service_type = 0")  + "AND s.service_active = ? ORDER BY p.person_username ASC;";

		if(DBConnection.doDebug())
			DBConnection.printDebug("getAllRequests", query);

		try
			{
				PreparedStatement ps = con.prepareStatement(query);

				ps.setInt(1, active);

				results = ps.executeQuery();
				while(results.next())
					{
						service = buildService(results);
						city = buildCity(results);
						country = buildCountry(results);
						customer = buildCustomer(results);

						city.setCountry(country);
						customer.setCity(city);
						service.setCustomer(customer);
						
						if(type)
							service.setPrice(results.getDouble("offer_price"));

						serviceList.add(service);

					} // end of while

				ps.close();
			} // end of try
		catch(Exception e)
			{
				if(DBConnection.doDebug())
					{
						DBConnection.printDebug("getAllRequests", query);
						System.out.println("error query exception - getAllRequests - SELECT: " + e);
						e.printStackTrace();
					} // end of if
			} // end of catch

		return serviceList;
	}

	@Override
	public int createServices(int reqID,Service service, boolean type)
			throws SQLException
			{
				int rc = -1;
				ResultSet results;

				String query_check = "SELECT service_name FROM service WHERE service_name = ?;";

				if(DBConnection.doDebug())
					{
						DBConnection.printDebug("createService", query_check);
					} // end of if

				try
					{
						PreparedStatement ps = con.prepareStatement(query_check);
						ps.setString(1,  service.getName());

						results = ps.executeQuery();

						if(!results.next())
							{
								String query = "INSERT INTO service(service_drawing_number, service_name," +
										" service_prod_quantity, service_description, service_deadline," +
										" service_customer_id,service_dimensions, service_type, service_active)" +
										" VALUES( ?, ?, ?, ?, ?, ?, ?,"+(type ? "'1'" : "'0'") +" ,'1');";

								if(DBConnection.doDebug())
									DBConnection.printDebug("createRequest", query);

								try
									{
										ps = con.prepareStatement(query, PreparedStatement.RETURN_GENERATED_KEYS);

										ps.setInt(1, service.getDrawingNo());
										ps.setString(2, service.getName());
										ps.setInt(3, service.getProdQuantity());
										ps.setString(4, service.getDescription());
										ps.setString(5, service.getDeadline());
										ps.setInt(6, service.getCustomer().getPersonID());
										ps.setString(7, service.getDimensions());

										rc = ps.executeUpdate();
										if(type)
											{
												ResultSet insertedRow = ps.getGeneratedKeys();
												
												if(rc > 0 && insertedRow.next())
													{
														String query_2 = "INSERT INTO offer(offer_price, offer_service_id" + (reqID != 0 ? ", offer_request_id)" : ")") + " VALUES (?, ?" + (reqID != 0 ? ",?);" : ")");
														
														try
															{
																ps = con.prepareStatement(query_2);
																
																ps.setDouble(1, service.getPrice());
																ps.setInt(2, insertedRow.getInt(1));
																if(reqID != 0)
																ps.setInt(3, reqID);
																
																
																
																
																if(DBConnection.doDebug())
																	{
																		DBConnection.printDebug("createService", query_2);
																	} // end of if
																
																rc = ps.executeUpdate();
															} // end of try
														catch(Exception e)
															{

																if(DBConnection.doDebug())
																	{
																		DBConnection.printDebug("createService", query_2);
																		e.printStackTrace();
																	} // end of if
																rc = -1;
															} // end of catch
													} // end of if
											} // end of if
									} // end of try
								catch(Exception e)
									{
										if(DBConnection.doDebug())
											{
												DBConnection.printDebug("createService", query);
												e.printStackTrace();
											} // end of if
										rc = -1;
									} // end of catch
							} // end of if
						else
							{
								rc = -1;
							} // end of else
						ps.close();
					}
				catch(Exception e)
					{
						if(DBConnection.doDebug())
							{
								DBConnection.printDebug("createService", query_check);
								e.printStackTrace();
							} // end of if
					}
				return rc;
			}

	@Override
	public int updateServices(Service service, boolean type) throws SQLException
	{
		int rc = -1;

		String query = "UPDATE service SET service_drawing_number = ?, service_name = ?, service_prod_quantity = ? ,"
				+ "service_description = ?, service_dimensions = ?,service_deadline = ?, service_customer_id = ? WHERE service_id = ?;";

		if(DBConnection.doDebug())
			DBConnection.printDebug("updateServices", query);

		try
			{
				PreparedStatement ps = con.prepareStatement(query, PreparedStatement.RETURN_GENERATED_KEYS);

				ps.setInt(1, service.getDrawingNo());
				ps.setString(2, service.getName());
				ps.setInt(3, service.getProdQuantity());
				ps.setString(4, service.getDescription());
				ps.setString(5, service.getDimensions());
				ps.setString(6, service.getDeadline());
				ps.setInt(7, service.getCustomer().getPersonID());
				ps.setInt(8, service.getServiceID());

				rc = ps.executeUpdate();
				if(type)
					{
						ResultSet insertedRow = ps.getGeneratedKeys();
						
						if(rc > 0 && insertedRow.next())
							{
								String query_2 = "UPDATE service_prices SET service_prices_amount = ? WHERE service_prices_sv = ?;";
								
								try
									{
										ps = con.prepareStatement(query_2);
										
										ps.setDouble(1, service.getPrice());
										ps.setDouble(2, insertedRow.getInt(1));
										
										
										rc = ps.executeUpdate();
										
										if(DBConnection.doDebug())
											{
												DBConnection.printDebug("updateService", query_2);
											} // end of if
									} // end of try
								catch(Exception e)
									{

										if(DBConnection.doDebug())
											{
												DBConnection.printDebug("updateService", query_2);
												e.printStackTrace();
											} // end of if
									} // end of catch
							} // end of if
					} // end of if
							
				ps.close();
				
			} // end of try
			catch(Exception e)
				{
					if(DBConnection.doDebug())
						{
							DBConnection.printDebug("updateServices Error: ", query);
							e.printStackTrace();
						} // end of if
				} // end of catch

		return rc;
	}

	@Override
	public int changeStatus(int status, int id) throws SQLException
	{
		int rc = -1;

		String query = "UPDATE service SET service_active = ? WHERE service_id = ?";

		if(DBConnection.doDebug())
			DBConnection.printDebug("changeStatus", query);

		try
			{
				PreparedStatement ps = con.prepareStatement(query);

				ps.setInt(1, status);
				ps.setInt(2, id);

				rc = ps.executeUpdate();

				ps.close();
			} // end of try
		catch(Exception e)
			{
				if(DBConnection.doDebug())
					{
						DBConnection.printDebug("changeStatus", query);
						e.printStackTrace();
					} // end of if
			} // end of catch
		return rc;
	}

	@Override
	public Service getOne(int id) throws SQLException
	{
		ResultSet results;
		Service service = new Service();
		Country country = new Country();
		Customer customer = new Customer();
		City city = new City();

		String query = "SELECT s.*, p.person_username, p.person_email, p.person_phoneno, c.city_name, c.city_id, ct.country_name, ct.country_id, ct.country_tld," +
				" cus.customer_city_id, cus.customer_id FROM service AS s LEFT JOIN person AS p ON s.service_customer_id = p.person_id" +
				" LEFT JOIN customer AS cus ON p.person_id = cus.customer_id LEFT JOIN city AS c ON c.city_id = cus.customer_city_id" +
				"  LEFT JOIN country AS ct ON c.city_country_id = ct.country_id WHERE s.service_id = ? ORDER BY p.person_username ASC;";

		if(DBConnection.doDebug())
			DBConnection.printDebug("getOne", query);

		try
			{
				PreparedStatement ps = con.prepareStatement(query);

				ps.setInt(1, id);

				results = ps.executeQuery();
				if(results.next())
					{
						service = buildService(results);
						city = buildCity(results);
						country = buildCountry(results);
						customer = buildCustomer(results);

						city.setCountry(country);
						customer.setCity(city);
						service.setCustomer(customer);

					} // end of while

				ps.close();
			} // end of try
		catch(Exception e)
			{
				if(DBConnection.doDebug())
					{
						DBConnection.printDebug("getOne", query);
						System.out.println("error query exception - getOne - SELECT: " + e);
						e.printStackTrace();
					} // end of if
			} // end of catch

		return service;
	}

	@Override
	public ArrayList<Service> searchServicesByCity(String city,
			boolean active, boolean type) throws SQLException
			{
				return searchService(type,active, "c.city_name LIKE ?", 1, city);
			}

	@Override
	public ArrayList<Service> searchServicesByDimension(String dimension,
			boolean active, boolean type) throws SQLException
			{
				return searchService(type,active, "s.service_dimension LIKE ?", 1, dimension);
			}

	@Override
	public ArrayList<Service> searchServicesByCountry(String country,
			boolean active, boolean type) throws SQLException
			{
				return searchService(type,active, "ct.country_name LIKE ?", 1, country);
			}

	@Override
	public ArrayList<Service> searchServicesByCountryTld(
			String country_tld, boolean active, boolean type)
					throws SQLException
					{
						return searchService(type,active, "ct.country_tld LIKE ?", 1, country_tld);
					}

	@Override
	public ArrayList<Service> searchServicesByDeadline(String deadline,
			boolean active, boolean type) throws SQLException
			{
				return searchService(type,active, "s.service_deadline LIKE ?", 1, deadline);
			}

	@Override
	public ArrayList<Service> searchServicesByCustomer(String customer,
			boolean active, boolean type) throws SQLException
			{
				return searchService(type,active, "p.person_username LIKE ?", 1, customer);
			}

	@Override
	public ArrayList<Service> searchServicesByDrawingNo(int drawingNo,
			boolean active, boolean type) throws SQLException
			{
				return searchService(type,active, "s.service_drawing_number LIKE ?", 1, String.valueOf(drawingNo));
			}

	@Override
	public ArrayList<Service> searchServicesByProdQuantity(int quantity,
			boolean active, boolean type) throws SQLException
			{
				return searchService(type,active, "s.service_prod_quantity LIKE ?", 1, String.valueOf(quantity));
			}

	@Override
	public ArrayList<Service> searchServicesByName(String name,
			boolean active, boolean type) throws SQLException
			{
				return searchService(type,active, "s.service_name LIKE ?", 1, name);
			}

	@Override
	public ArrayList<Service> searchServiceByPrice(double price,
			boolean active, boolean type)
			{
				return searchService(type,active, "sv.service_prices_amount LIKE ?", 1, String.valueOf(price));
			}

	@Override
	public int transferServices(int reqID,Service service, boolean type)
			throws SQLException
		{
			int rc = -1;

							String query = "INSERT INTO service(service_drawing_number, service_name," +
									" service_prod_quantity, service_description, service_deadline," +
									" service_customer_id,service_dimensions, service_type, service_active)" +
									" VALUES( ?, ?, ?, ?, ?, ?, ?,"+(type ? "'1'" : "'0'") +" ,'1');";

							if(DBConnection.doDebug())
								DBConnection.printDebug("transferServices", query);

							try
								{
									PreparedStatement ps = con.prepareStatement(query, PreparedStatement.RETURN_GENERATED_KEYS);

									ps.setInt(1, service.getDrawingNo());
									ps.setString(2, service.getName());
									ps.setInt(3, service.getProdQuantity());
									ps.setString(4, service.getDescription());
									ps.setString(5, service.getDeadline());
									ps.setInt(6, service.getCustomer().getPersonID());
									ps.setString(7, service.getDimensions());

									rc = ps.executeUpdate();
									
									ResultSet insertedRow = ps.getGeneratedKeys();
											
											if(rc > 0 && insertedRow.next())
												{
													String query_2 = "INSERT INTO offer(offer_price, offer_request_id, offer_service_id) VALUES (?, ?, ?);";
													
													try
														{
															ps = con.prepareStatement(query_2);
															
															ps.setDouble(1, service.getPrice());
															ps.setInt(2, reqID);
															ps.setInt(3, insertedRow.getInt(1));
															
															if(DBConnection.doDebug())
																{
																	DBConnection.printDebug("transferServices", query_2);
																} // end of if
															
															rc = ps.executeUpdate();
														} // end of try
													catch(Exception e)
														{

															if(DBConnection.doDebug())
																{
																	DBConnection.printDebug("transferServices", query_2);
																	e.printStackTrace();
																} // end of if
														} // end of catch
												} // end of if
									ps.close();
								} // end of try
							catch(Exception e)
								{
									if(DBConnection.doDebug())
										{
											DBConnection.printDebug("transferServices", query);
											e.printStackTrace();
										} // end of if
								} // end of catch
					
			return rc;
		}

}
